using Dapper;
using Finance.Api.Read.Applications.Commands.台账管理;
using Finance.Api.Read.Dtos;
using Finance.Domain;
using Finance.ErrorCode;
using Finance.Infrastructure;
using MediatR;
using System.Text;

namespace Finance.Api.Read.Applications.CommandHands.台账管理
{
    public class ProductBatchQueryCommandHandlers : IRequestHandler<ProductBatchQueryCommand, ApiResult<ApiPaging<List<ProductBatchDto>>>>
    {
        private readonly IDapperDb _db;
        private readonly ILogger<ProductBatchQueryCommandHandlers> _logger;

        public ProductBatchQueryCommandHandlers(IDapperDb db, ILogger<ProductBatchQueryCommandHandlers> logger)
        {
            _db = db;
            _logger = logger;
        }

        public async Task<ApiResult<ApiPaging<List<ProductBatchDto>>>> Handle(ProductBatchQueryCommand request, CancellationToken cancellationToken)
        {
            var result = new ApiResult<ApiPaging<List<ProductBatchDto>>>();
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder totalNum = new StringBuilder();

            ApiPaging<List<ProductBatchDto>> res = new ApiPaging<List<ProductBatchDto>>();
            var parameters = new DynamicParameters();
            try
            {
                stringBuilder.Append(@"SELECT 
                            a.Id,
                            a.ProductNo,
                            a.ProductName,
                            a.ProductType,
                            a.BatchNum,
                            a.Location,
                            a.ProductionDate,
                            a.OverProductDate,
                            a.ProductNum
                    FROM Product a
                    WHERE 1=1
                            AND a.IsDelete=0 ");

                // 货品名称查询条件
                if (!string.IsNullOrEmpty(request.ProductName))
                {
                    stringBuilder.Append(" AND a.ProductName LIKE @ProductName ");
                    parameters.Add("ProductName", $"%{request.ProductName}%");
                }

                // 货品类型查询条件
                if (!string.IsNullOrEmpty(request.ProductType))
                {
                    stringBuilder.Append(" AND a.ProductType = @ProductType ");
                    parameters.Add("ProductType", request.ProductType);
                }

                // 批次号查询条件
                if (!string.IsNullOrEmpty(request.BatchNum))
                {
                    stringBuilder.Append(" AND a.BatchNum LIKE @BatchNum ");
                    parameters.Add("BatchNum", $"%{request.BatchNum}%");
                }

                // 库名查询条件
                if (!string.IsNullOrEmpty(request.Location))
                {
                    stringBuilder.Append(" AND a.Location = @Location ");
                    parameters.Add("Location", request.Location);
                }

                // 生产日期开始查询条件
                if (request.ProductionDateStart.HasValue)
                {
                    stringBuilder.Append(" AND a.ProductionDate >= @ProductionDateStart ");
                    parameters.Add("ProductionDateStart", request.ProductionDateStart.Value);
                }

                // 生产日期结束查询条件
                if (request.ProductionDateEnd.HasValue)
                {
                    stringBuilder.Append(" AND a.ProductionDate <= @ProductionDateEnd ");
                    parameters.Add("ProductionDateEnd", request.ProductionDateEnd.Value);
                }

                // 到期日期开始查询条件
                if (request.OverProductDateStart.HasValue)
                {
                    stringBuilder.Append(" AND a.OverProductDate >= @OverProductDateStart ");
                    parameters.Add("OverProductDateStart", request.OverProductDateStart.Value);
                }

                // 到期日期结束查询条件
                if (request.OverProductDateEnd.HasValue)
                {
                    stringBuilder.Append(" AND a.OverProductDate <= @OverProductDateEnd ");
                    parameters.Add("OverProductDateEnd", request.OverProductDateEnd.Value);
                }

                // 构建总数查询SQL
                totalNum.Append(@"SELECT COUNT(1)
                            FROM Product a
                            WHERE 1=1
                                    AND a.IsDelete=0 ");

                // 为总数查询添加相同的查询条件
                if (!string.IsNullOrEmpty(request.ProductName))
                {
                    totalNum.Append(" AND a.ProductName LIKE @ProductName ");
                }

                if (!string.IsNullOrEmpty(request.ProductType))
                {
                    totalNum.Append(" AND a.ProductType = @ProductType ");
                }

                if (!string.IsNullOrEmpty(request.BatchNum))
                {
                    totalNum.Append(" AND a.BatchNum LIKE @BatchNum ");
                }

                if (!string.IsNullOrEmpty(request.Location))
                {
                    totalNum.Append(" AND a.Location = @Location ");
                }

                if (request.ProductionDateStart.HasValue)
                {
                    totalNum.Append(" AND a.ProductionDate >= @ProductionDateStart ");
                }

                if (request.ProductionDateEnd.HasValue)
                {
                    totalNum.Append(" AND a.ProductionDate <= @ProductionDateEnd ");
                }

                if (request.OverProductDateStart.HasValue)
                {
                    totalNum.Append(" AND a.OverProductDate >= @OverProductDateStart ");
                }

                if (request.OverProductDateEnd.HasValue)
                {
                    totalNum.Append(" AND a.OverProductDate <= @OverProductDateEnd ");
                }

                // 执行总数查询
                int totalCount = await _db.QueryFirst<int>(totalNum.ToString(), parameters);

                // 添加分页和排序
                stringBuilder.Append($" ORDER BY a.Id DESC LIMIT {((request.index - 1) * request.size)}, {request.size}");

                // 执行数据查询
                var list = await _db.Query<ProductBatchDto>(stringBuilder.ToString(), parameters);
                
                // 手动设置序号
                int rowNumber = (request.index - 1) * request.size + 1;
                foreach (var item in list)
                {
                    item.RowNum = rowNumber++;
                }
                
                res.data = list.ToList();
                res.totalcount = totalCount;
                res.totalpcount = (int)Math.Ceiling(totalCount * 1.0 / request.size);
                result.data = res;
                result.message = "批次分页查询成功";
                result.Code = Enumcode.ok;
            }
            catch (Exception ex)
            {
                _logger.LogError("批次分页查询失败: {Message}", ex.Message);
                result.message = "批次分页查询失败";
                result.Code = Enumcode.fail;
                throw;
            }

            return await Task.FromResult(result);
        }
    }
} 